package com.example.dao;

import com.example.model.Student;
import com.example.util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDAOImpl implements StudentDAO {

    // 学生登录验证
    @Override
    public Student findByStudentIdAndPassword(String studentId, String password) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Student student = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT s.*, c.className FROM students s " +
                    "JOIN classes c ON s.classId = c.classId " +
                    "WHERE s.studentId = ? AND s.studentPwd = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, studentId);
            pstmt.setString(2, password);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                student = new Student();
                student.setStudentId(rs.getString("studentId"));
                student.setStudentName(rs.getString("studentName"));
                student.setStudentPwd(rs.getString("studentPwd"));
                student.setSex(rs.getString("sex"));
                student.setClassId(rs.getInt("classId"));
                student.setClassName(rs.getString("className"));  // 联表查询得到的班级名称
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return student;
    }

    // 查询所有学生（带班级名称）
    @Override
    public List<Student> findAll() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT s.*, c.className FROM students s " +
                    "JOIN classes c ON s.classId = c.classId";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Student student = new Student();
                student.setStudentId(rs.getString("studentId"));
                student.setStudentName(rs.getString("studentName"));
                student.setStudentPwd(rs.getString("studentPwd"));
                student.setSex(rs.getString("sex"));
                student.setClassId(rs.getInt("classId"));
                student.setClassName(rs.getString("className"));
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return students;
    }

    // 添加学生
    @Override
    public int insert(Student student) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = 0;

        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO students(studentId, studentName, studentPwd, sex, classId) " +
                    "VALUES(?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, student.getStudentId());
            pstmt.setString(2, student.getStudentName());
            pstmt.setString(3, student.getStudentPwd());
            pstmt.setString(4, student.getSex());
            pstmt.setInt(5, student.getClassId());
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return result;
    }

    // 修改学生信息
    @Override
    public int update(Student student) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = 0;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE students SET studentName=?, studentPwd=?, sex=?, classId=? " +
                    "WHERE studentId=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, student.getStudentName());
            pstmt.setString(2, student.getStudentPwd());
            pstmt.setString(3, student.getSex());
            pstmt.setInt(4, student.getClassId());
            pstmt.setString(5, student.getStudentId());
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return result;
    }

    // 删除学生
    @Override
    public int delete(String studentId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = 0;

        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM students WHERE studentId=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, studentId);
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return result;
    }

    // 模糊查询学生信息
    @Override
    public List<Student> searchStudents(String studentId, String studentName, String sex) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            StringBuilder sql = new StringBuilder("SELECT s.*, c.className FROM students s JOIN classes c ON s.classId = c.classId WHERE 1=1");

            // 动态拼接查询条件
            if (studentId != null && !studentId.isEmpty()) {
                sql.append(" AND s.studentId LIKE ?");
            }
            if (studentName != null && !studentName.isEmpty()) {
                sql.append(" AND s.studentName LIKE ?");
            }
            if (sex != null && !sex.isEmpty()) {
                sql.append(" AND s.sex = ?");
            }

            pstmt = conn.prepareStatement(sql.toString());

            int index = 1;
            if (studentId != null && !studentId.isEmpty()) {
                pstmt.setString(index++, "%" + studentId + "%");
            }
            if (studentName != null && !studentName.isEmpty()) {
                pstmt.setString(index++, "%" + studentName + "%");
            }
            if (sex != null && !sex.isEmpty()) {
                pstmt.setString(index, sex);
            }

            rs = pstmt.executeQuery();

            while (rs.next()) {
                Student student = new Student();
                student.setStudentId(rs.getString("studentId"));
                student.setStudentName(rs.getString("studentName"));
                student.setSex(rs.getString("sex"));
                student.setClassId(rs.getInt("classId"));
                student.setClassName(rs.getString("className"));
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return students;
    }

    @Override
    public boolean checkStudentIdExists(String studentId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean exists = false;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT studentId FROM students WHERE studentId = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, studentId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                exists = true; // 学号已存在
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return exists;
    }
}